﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Linq;
using MISAPP.Common;
using System.Data.Common;


namespace MISAPP.Classify.DataAccess
{
    public enum SEQUARED_LEVEL
    {
        SEQUARED_ALL, SEQUARED_1, SEQUARED_2, SEQUARED_3
    }

    public class DA_UserInfo:DA_Matrix
    {

        //protected List<string> insertList = null;
        //protected bool setTableSchema = false;
        public System.Data.DataTable GetUserInfo(string p_id, string p_pw)
        {
            DataTable dt = null;
            string sql;
            StringBuilder sb = new StringBuilder();

            sql = "SELECT DISTINCT TEAM FROM MISTeam";
            dt = getData(sql, "MISTeam");


            foreach (DataRow row in dt.Rows)
            {
                sb.AppendFormat(",MAX(CASE B.TEAM WHEN '{0}' THEN B.team ELSE '' END) {0} \r\n", row[0].ToString());
            }


            sql = @"SELECT A.user_id
                        ,A.SEQUARED_LEVEL
                        {0}
                    FROM 
	                    (SELECT *
	                    FROM MISUser A
	                    WHERE A.user_id = @user_id AND A.user_pw = @user_pw
	                    )A LEFT OUTER JOIN MISTeam B
	                    ON A.user_id = B.user_id
                    group by A.user_id,A.SEQUARED_LEVEL
";
            FillTableSchema("MISUser");
            FillFieldList(new string[] { p_id }, new string[] { "@user_id" }, "user_id");
            FillFieldList(new string[] { p_pw }, new string[] { "@user_pw" }, "user_pw");

            sql = string.Format(sql, sb.ToString());
            AddParameter(sql);
            return getData();
        }

        public void SetLoginInfo(string p_id, string p_ip)
        {
            FillTableSchema("loged");
            string sql = string.Empty;
            sql = "INSERT INTO LOGED(LOGIN_ID,LOGIN_IP) VALUES(@LOGIN_ID,@LOGIN_IP)";
            FillFieldList(new string[] { p_id }, new string[] { "@LOGIN_ID" }, "login_id");
            FillFieldList(new string[] { p_ip }, new string[] { "@LOGIN_IP" }, "login_ip");
            AddParameter(sql);
            Common.DAL.ExecuteNoneQuery(cmd);

        }

        /// <summary>
        /// 找出該使用者要輸入的類別，目前只有三個等級，0為不選擇
        /// </summary>
        /// <param name="p_level"></param>
        /// <returns></returns>
        public DataTable GetSequared(SEQUARED_LEVEL p_level,string p_fldName)
        {

//            FillTableSchema("MISParameter");
////            string sql = @" 
////SELECT *
////FROM [MISParameter]
////WHERE team = 'SEQUARED_LEVEL' 
////{0} ";
////            if (p_level == SEQUARED_LEVEL.SEQUARED_ALL)
////            { }
//            //AND para1 = @para1

//            string sql, var;
//            var = " 1=1 ";

//            FillFieldList(new string[] { "SEQUARED" }, new string[] { "@SEQUARED" }, "team");
//            if (p_level == SEQUARED_LEVEL.SEQUARED_1)
//            {
//                var = " A.fldname = @fldname";
//                FillFieldList(new string[] { p_fldName }, new string[] { "@fldname" }, "fldname");
//            }           
//            sql =string.Format( @" 
//SELECT A.fldvalue
//FROM [MISParameter] AS A
//WHERE team = @SEQUARED 
//AND {0}",var);
//            //A.fldname = 'CIB'            
//            AddParameter(sql);
//            return getData();
            return null;
        }


        public override string[] GetDistinctField()
        {
            return new string[]{"user_id"};
        }

        public override DataSet GetCriteria()
        {
            string[] criterias = GetDistinctField();
            string sql = "SELECT user_id,SEQUARED_LEVEL FROM MISUser ORDER BY 1";
            DataSet ds = new DataSet();
            DataTable dt = null;

            foreach (string fld in criterias)
            {
                dt = getData(sql , fld);
                ds.Tables.Add(dt);
            }
            return ds;
        }

        public int ChangeUserPW(string p_id, string p_oldPW, string p_newPW)
        {
            string sql;
            sql = @"
UPDATE MISUSER SET user_pw =@p_newPW WHERE user_id = @p_id AND user_pw = @p_oldPW
";
            FillTableSchema("MISUser");
            FillFieldList(new string[] { p_id }, new string[] { "@p_id" }, "user_id");
            FillFieldList(new string[] { p_oldPW }, new string[] { "@p_oldPW" }, "user_pw");
            FillFieldList(new string[] { p_newPW }, new string[] { "@p_newPW" }, "user_pw");

            AddParameter(sql);
            int iResult = Common.DAL.ExecuteNoneQuery(cmd);
            return iResult;
        }
        
    }
}
